package org.sd.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xddf.usermodel.PresetColor;
import org.apache.poi.xddf.usermodel.XDDFColor;
import org.apache.poi.xddf.usermodel.XDDFSolidFillProperties;
import org.apache.poi.xddf.usermodel.chart.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTDLbls;
import org.springframework.util.ResourceUtils;

import java.io.*;
import java.util.*;

/**
 * ClassName: ExcelChartUtils <br/>
 * Description: 导出excel中绘制图表<br/>
 * date: 2022/11/17 11:39<br/>
 *
 * @author DELL<br />
 * @since JDK 1.8
 */
public class ExcelChartUtils2 {

    //public static final String READ_FILE_PATH = "F:\\excel\\demo_data.xls";
    public static final String READ_FILE_PATH = "classpath:demo_data.xls";

    public static void main(String[] args) throws IOException {
        ExcelChartUtils2 excelChartUtils=new ExcelChartUtils2();
        XSSFWorkbook wb = new XSSFWorkbook();
//        String sheetName = "当月业务量统计";
//        String sheetName2 = "本周高频报错业务统计";
//        String sheetName3 = "本周高频报错占比统计";
//        excelChartUtils.exportLineDiagram(wb,sheetName2);
//        excelChartUtils.exportPieDirgram(wb,sheetName3);
//        excelChartUtils.exportBarDiagram(wb,sheetName);

    }

    public void exportPieDirgram(XSSFWorkbook wb,String sheetName) throws FileNotFoundException {
        XSSFSheet sheet = wb.createSheet(sheetName);
        // 创建一个画布
        XSSFDrawing drawing = sheet.createDrawingPatriarch();
        // 前四个默认0，[0,4]：从0列4行开始;[7,20]:到7列20行结束
        // 默认宽度(14-8)*12
        XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 30, 40);
        // 创建一个chart对象
        XSSFChart chart = drawing.createChart(anchor);
        // 标题
        chart.setTitleText("高频报错占比统计");
        // 标题是否覆盖图表
        chart.setTitleOverlay(false);

        // 图例位置
        XDDFChartLegend legend = chart.getOrAddLegend();
        legend.setPosition(LegendPosition.TOP_RIGHT);

        //解析数据
        Map<String, List<String>> dataMap = ExcelChartUtils2.getData(READ_FILE_PATH,3,0,6);;
        List<String> keylist = dataMap.get("key");
        List<String> valueList = dataMap.get("value");
        List<Integer> vList = new ArrayList<>();
        for(int i = 0;i<valueList.size();i++){
            if(valueList.get(i).isEmpty()){
                vList.add(0);
            }else{
                vList.add(Integer.valueOf(valueList.get(i).split("\\.")[0]));
            }
        }
        //XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(0, 0, 0, 6));//从指定表格中的位置上获取对应数据
        XDDFCategoryDataSource countries = XDDFDataSourcesFactory.fromArray(keylist.toArray(new String[keylist.size()]));
        // 数据1，单元格范围位置[1, 0]到[1, 6]
        // XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, 6));
        XDDFNumericalDataSource<Integer> area = XDDFDataSourcesFactory.fromArray(vList.toArray(new Integer[vList.size()]));
        // XDDFChartData data = chart.createData(ChartTypes.PIE3D, null, null);
        XDDFChartData data = chart.createData(ChartTypes.PIE, null, null);
        // 设置为可变颜色
        data.setVaryColors(true);
        // 图表加载数据
        data.addSeries(countries, area);

        // 绘制
        chart.plot(data);
        CTDLbls dLbls = chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).addNewDLbls();
        dLbls.addNewShowVal().setVal(false);
        dLbls.addNewShowLegendKey().setVal(false);
        dLbls.addNewShowCatName().setVal(true);// 类别名称
        dLbls.addNewShowSerName().setVal(false);
        dLbls.addNewShowPercent().setVal(true);// 百分比
        dLbls.addNewShowLeaderLines().setVal(true);// 引导线
        dLbls.setSeparator("\n");// 分隔符为分行符
        dLbls.addNewDLblPos().setVal(org.openxmlformats.schemas.drawingml.x2006.chart.STDLblPos.Enum.forString("inEnd"));// 数据标签内

        // 打印图表的xml
        // System.out.println(chart.getCTChart());

    }

    public void exportLineDiagram(XSSFWorkbook wb,String sheetName) throws IOException {
        FileOutputStream fileOut = null;
        XSSFSheet sheet = wb.createSheet(sheetName);
        // 创建一个画布
        XSSFDrawing drawing = sheet.createDrawingPatriarch();
        // 前四个默认0，[0,5]：从0列5行开始;[7,26]:到7列26行结束
        // 默认宽度(14-8)*12
        XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 30, 40);
        // 创建一个chart对象
        XSSFChart chart = drawing.createChart(anchor);
        // 标题
        chart.setTitleText("高频报错业务统计");
        // 标题覆盖
        chart.setTitleOverlay(false);

        // 图例位置
        XDDFChartLegend legend = chart.getOrAddLegend();
        legend.setPosition(LegendPosition.TOP);

        // 分类轴标(X轴),标题位置
        XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.LEFT);
        //    bottomAxis.setTitle("国家");
        // 值(Y轴)轴,标题位置
        XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.BOTTOM);
        //  leftAxis.setTitle("面积大小");
        Map<String, List<String>> dataMap = ExcelChartUtils2.getData(READ_FILE_PATH,3,2,6);
        List<String> keylist = dataMap.get("key");
        List<String> valueList = dataMap.get("value");
        List<Integer> vList = new ArrayList<>();
        for(int i = 0;i<valueList.size();i++){
            if(valueList.get(i).isEmpty()){
                vList.add(0);
            }else{
                vList.add(Integer.valueOf(valueList.get(i).split("\\.")[0]));
            }
        }
//            keylist.remove(0);
//            valueList.remove(0);
        // CellRangeAddress(起始行号，终止行号， 起始列号，终止列号）
        // 分类轴标(X轴)数据，单元格范围位置[0, 0]到[0, 6]
        //XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(0, 0, 0, 6));//从指定表格中的位置上获取对应数据
        XDDFCategoryDataSource countries = XDDFDataSourcesFactory.fromArray(keylist.toArray(new String[keylist.size()]));
        // 数据1，单元格范围位置[1, 0]到[1, 6]
        // XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, 6));
        XDDFNumericalDataSource<Integer> area = XDDFDataSourcesFactory.fromArray(vList.toArray(new Integer[vList.size()]));

        // bar：条形图，
        XDDFLineChartData bar = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);

        leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
        // 设置为可变颜色
        bar.setVaryColors(false);// 如果需要设置成自己想要的颜色，这里可变颜色要设置成false

        // 图表加载数据，条形图
        XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) bar.addSeries(countries, area);
        // 条形图例标题
        series1.setTitle("业务量统计", null);
        XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.BLUE));
        // 条形图，填充颜色
        series1.setFillProperties(fill);

        // 绘制
        chart.plot(bar);
        // CTBarSer ser = chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(0);
        // CTLegend legend2 = chart.getCTChartSpace().getChart().getLegend();//更详细的图例设置

        // 打印图表的xml
        System.out.println(chart.getCTChart());


    }

    public String exportBarDiagram(XSSFWorkbook wb,String sheetName) throws IOException {

        FileOutputStream fileOut = null;
        try {
            XSSFSheet sheet = wb.createSheet(sheetName);
            // 创建一个画布
            XSSFDrawing drawing = sheet.createDrawingPatriarch();
            // 前四个默认0，[0,5]：从0列5行开始;[7,26]:到7列26行结束
            // 默认宽度(14-8)*12
            XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 30, 40);
            // 创建一个chart对象
            XSSFChart chart = drawing.createChart(anchor);
            // 标题
            chart.setTitleText("业务量统计");
            // 标题覆盖
            chart.setTitleOverlay(false);

            // 图例位置
            XDDFChartLegend legend = chart.getOrAddLegend();
            legend.setPosition(LegendPosition.TOP);

            // 分类轴标(X轴),标题位置
            XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
            //    bottomAxis.setTitle("国家");
            // 值(Y轴)轴,标题位置
            XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
            //  leftAxis.setTitle("面积大小");
            Map<String, List<String>> dataMap = ExcelChartUtils2.getData(READ_FILE_PATH,4,2,6);
            List<String> keylist = dataMap.get("key");
            List<String> valueList = dataMap.get("value");
            List<Integer> vList = new ArrayList<>();
            for(int i = 0;i<valueList.size();i++){
                if(valueList.get(i).isEmpty()){
                    vList.add(0);
                }else{
                    vList.add(Integer.valueOf(valueList.get(i).split("\\.")[0]));
                }
            }
//            keylist.remove(0);
//            valueList.remove(0);
            // CellRangeAddress(起始行号，终止行号， 起始列号，终止列号）
            // 分类轴标(X轴)数据，单元格范围位置[0, 0]到[0, 6]
            //XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(0, 0, 0, 6));//从指定表格中的位置上获取对应数据
            XDDFCategoryDataSource countries = XDDFDataSourcesFactory.fromArray(keylist.toArray(new String[keylist.size()]));
            // 数据1，单元格范围位置[1, 0]到[1, 6]
            // XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, 6));
            XDDFNumericalDataSource<Integer> area = XDDFDataSourcesFactory.fromArray(vList.toArray(new Integer[vList.size()]));

            // bar：条形图，
            XDDFBarChartData bar = (XDDFBarChartData) chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);

            leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
            // 设置为可变颜色
            bar.setVaryColors(false);// 如果需要设置成自己想要的颜色，这里可变颜色要设置成false
            // 条形图方向，纵向/横向：纵向
            bar.setBarDirection(BarDirection.COL);

            // 图表加载数据，条形图1
            XDDFBarChartData.Series series1 = (XDDFBarChartData.Series) bar.addSeries(countries, area);
            // 条形图例标题
            series1.setTitle("业务量统计", null);
            XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.BLUE));
            // 条形图，填充颜色
            series1.setFillProperties(fill);
            // 绘制
            chart.plot(bar);
            // CTBarSer ser = chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(0);
            // CTLegend legend2 = chart.getCTChartSpace().getChart().getLegend();//更详细的图例设置

            // 打印图表的xml
            System.out.println(chart.getCTChart());

            // 将输出写入excel文件
            // String filename = "排行榜前七的国家.xlsx";
            //  out = new FileOutputStream(getAbsoluteFile(filename));
            String filename = encodingFilename();
            fileOut = new FileOutputStream("E:\\"+File.separator+filename);
            wb.write(fileOut);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            wb.close();
            if (fileOut != null) {
                fileOut.close();
            }
        }

        return "success";
    }

    public static Map<String,List<String>> getData(String filePath, int sheetIndex,int keyNum,int valueNum) throws FileNotFoundException {
        File file = ResourceUtils.getFile(filePath);
        Map<String, List<String>> resMap = new HashMap<>();
        FileInputStream fis = null;
        HSSFWorkbook workbook = null;
        List<String> keylist = new ArrayList();
        List<String> valueList = new ArrayList<>();
        try {
            fis = new FileInputStream(file);
            workbook = new HSSFWorkbook(fis);
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            Row row;
            Cell cell;
            for (int i = 1; i < sheet.getLastRowNum(); i++) {
                row = sheet.getRow(i);
                cell = row.getCell(keyNum); // 0 is the column index
                System.out.println(cell.getStringCellValue());
                keylist.add(cell.getStringCellValue());
                cell = row.getCell(valueNum); // 0 is the column index
                double numericCellValue;
                if(cell.getCellType().equals(CellType.NUMERIC)){
                    numericCellValue = cell.getNumericCellValue();
                }else {
                    numericCellValue = Double.valueOf(cell.getStringCellValue());
                }
                valueList.add( String.valueOf(numericCellValue) );
            }
            System.out.println("name:"+keylist);
            System.out.println("value:"+valueList);
            workbook.close();
            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
//        resMap.put("key",keylist.toArray(new String[keylist.size()]));
//        resMap.put("value",valueList.toArray(new String[valueList.size()]));
        resMap.put("key",keylist);
        resMap.put("value",valueList);
        return resMap;
    }




    /**
     * 编码文件名
     */
    public String encodingFilename()
    {
        String filename = UUID.randomUUID().toString() + ".xlsx";
        //String filename = "20230222.xlsx";
        return filename;
    }

    /**
     * 获取下载路径
     *
     * @param filename 文件名称
     */
    public  String getAbsoluteFile(String filename)
    {
        String downloadPath = "d://";
        File desc = new File(downloadPath);
        if (!desc.getParentFile().exists())
        {
            desc.getParentFile().mkdirs();
        }
        return downloadPath;
    }
}




